﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using System.Data.SqlClient;

namespace Clinica_Frba
{
    public partial class RegLleg : Form
    {
        Conexion sql = Conexion.getInstance();
        Utility util = Utility.getInstance();
        DateTime fechaActual;
        string[] tablaIdDias = { "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado", "Domingo" };
        decimal planMed;
        int idMed;
        int nAfRaiz;
        int turnoNum;

        public RegLleg()
        {
            InitializeComponent();
        }

        private void RegLleg_Load(object sender, EventArgs e)
        {
            fechaActual = DateTime.ParseExact(util.getAppDate() + " " + util.getAppHora(), "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture);
            labelFecAct.Text = fechaActual.ToString("dd/MM/yyyy HH:mm:ss");
            turnosGrid.CellClick += new DataGridViewCellEventHandler(turnosGrid_CellClick);
            bonosGrid.CellClick += new DataGridViewCellEventHandler(bonosGrid_CellClick);
        }

        private void loadBuscarProf()
        {
            //MessageBox.Show("Seleccione un médico");
            ListaProfesionales busquedaMed = new ListaProfesionales();
            try
            {
                idMed = busquedaMed.searchMedico();
                if (idMed > 0)
                {
                    idDoc.Text = Convert.ToString(idMed);
                    loadTurnosProf();
                }
                else
                {
                    idDoc.Clear();
                }
            }
            catch
            {
                idDoc.Clear();
            }
        }

        private void loadTurnosProf()
        {
            SqlDataAdapter adapter;
            DataTable table = new DataTable();

            try
            {
                string qSel, qFrom, qWhere, query;

                qSel = "SELECT Me.Medico_Apellido + ', ' + Me.Medico_Nombre as 'Medico', " +
                    "Tu.Turno_Numero as 'Turno', Pa.Paciente_Apellido + ', ' + Pa.Paciente_Nombre as 'Paciente', Pa.Id_Paciente as 'Id Paciente', " +
                    "Tu.Turno_Fecha as 'Fecha', Es.Esp_Descripcion as 'Especialidad' ";
                qFrom = "FROM b_sharp.Turnos as Tu " +
                        "JOIN b_sharp.Medicos as Me on Me.id_medico = Tu.Medico_ID " +
                        "JOIN b_sharp.Pacientes as Pa on Pa.Id_Paciente = Tu.Paciente_ID " +
                        "JOIN b_sharp.Especialidades as Es on Es.Cod_Especialidad = Tu.Medico_Especialidad ";
                qWhere = "WHERE Tu.Medico_ID = '" + Convert.ToString(idMed) + "' AND CONVERT(DATE,Tu.Turno_Fecha) = '" + fechaActual.ToString("yyyy-MM-dd") + "' " +
                        "AND NOT EXISTS (SELECT * FROM b_sharp.Cancelaciones WHERE Turno_Numero = Tu.Turno_Numero) " + 
                        "AND NOT EXISTS (SELECT * FROM b_sharp.Consultas WHERE Turno_Num = Tu.Turno_Numero) ";

                query = qSel + qFrom + qWhere + ";";
                adapter = sql.execQueryGrid(query);
                adapter.Fill(table);
                turnosGrid.DataSource = table;
                labelErrorTurGrid.Visible = false;
            }
            catch (SqlException)
            {
                labelErrorTurGrid.Text = "Error en la carga de turnos. Intente nuevamente.";
                labelErrorTurGrid.Visible = true;
            }
            
            
            if (turnosGrid.Rows.Count == 0)
            {
                MessageBox.Show("El profesional seleccionado no tiene turnos no cancelados en el día.");
                loadBuscarProf();
            }
            else
            {
                SelectColumn.Text = "Seleccionar";
                this.turnosGrid.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {this.SelectColumn});
                idAf.Focus();
                bonosGrid.Enabled = true;
                buttonBuscarBCon.Enabled = true;
                buttonIngBono.Enabled = true;
                idAf.Enabled = true;
                turnosGrid.Enabled = true;
                turnosGrid.ReadOnly = true;
                labelErrorIdAf.Text = "Ingrese número de Afiliado";
                labelErrorIdAf.Visible = true;
            }
        }



        void turnosGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 6)
            {
                verificarAf(Convert.ToString(turnosGrid[3, e.RowIndex].Value), Convert.ToDateTime(turnosGrid[4, e.RowIndex].Value), Convert.ToString(turnosGrid[1, e.RowIndex].Value));
            }
        }

        private void verificarAf (string idAf_Tur, DateTime fechaTur, string tur_Num)
        {
            if (idAf_Tur == idAf.Text && (fechaActual <= fechaTur.Subtract(new TimeSpan(0,15,0))))
            {
                turnosGrid.Enabled = false;
                idAf.Enabled = false;
                labelErrorIdAf.Visible = false;
                labelErrorIdAf2.Visible = false;
                buttonBuscarBCon.Enabled = true;
                buttonIngBono.Enabled = true;
                bonoCon.Enabled = true;
                bonoCon.ReadOnly = false;
                nAfRaiz = Convert.ToInt32(idAf.Text) / 100;
                turnoNum = Convert.ToInt32(tur_Num);
                loadBonos();
            }
            else
            {
                if (idAf_Tur != idAf.Text)
                {
                    labelErrorIdAf.Text = "Afiliado no coincide con ";
                    labelErrorIdAf2.Text = "turno seleccionado";
                    labelErrorIdAf.Visible = true;
                    labelErrorIdAf2.Visible = true;
                }
                else
                {
                    labelErrorIdAf.Text = "Ha llegado tarde.";
                    labelErrorIdAf2.Text = "El turno se ha cancelado.";
                    labelErrorIdAf.Visible = true;
                    labelErrorIdAf2.Visible = true;
                    cancelarTurno(tur_Num);
                }
            }
        }

        private void cancelarTurno(string turNum)
        {
            try
            {
                SqlCommand cancelar = new SqlCommand
                           ("INSERT INTO b_sharp.Cancelaciones " +
	                        "(Turno_Numero, Tipo_Cancelacion, Detalle_Cancelacion) " +
	                        "VALUES (@TurNum, '1', 'Paciente llega tarde');", sql.openCon());
                cancelar.Parameters.AddWithValue("@TurNum", turNum);
                cancelar.ExecuteNonQuery();
                labelErrorTurGrid.Visible = false;
                buttonLimpiar_Click(this, null);
                loadTurnosProf();
            }
            catch (SqlException)
            {
                labelErrorTurGrid.Text = "Error en la cancelación.";
                labelErrorTurGrid.Visible = true;
            }

        }

        private void loadBonos()
        {
            try
            {
                SqlCommand query = new SqlCommand
                    ("SELECT Plan_Med_Codigo " +
                    "FROM b_sharp.Pacientes " +
                    "WHERE Id_Paciente = @idPa;", sql.openCon());
                query.Parameters.AddWithValue("@idPa", idAf.Text);
                planMed = Convert.ToDecimal(query.ExecuteScalar());
                groupBoxBono.Enabled = true;
                buttonBuscarBCon.Enabled = true;
                buttonIngBono.Enabled = true;
                bonosGrid.Enabled = true;
                labelFecAct.Enabled = true;
                labelBCon.Enabled = true;
                MessageBox.Show("Ingrese un número de bono o busque uno disponible");
            }
            catch (SqlException)
            {
                MessageBox.Show("Plan Medico no encontrado");
            }
        }

        private void buttonIngBono_Click(object sender, EventArgs e)
        {
            if (bonosGrid.Columns.Count == 4)
            {
                bonosGrid.Columns.RemoveAt(3);
            }
            SqlDataAdapter adapter;
            DataTable table = new DataTable();

            try
            {
                string query = "SELECT BC.BonoConsulta_Num as 'Numero de Bono', BC.PlanMed_Cod as 'Plan Medico', BC.Id_Paciente_Compra as 'ID Paciente Compra' " +
                                "FROM b_sharp.BonoConsulta as BC " +
                                "WHERE BC.Id_Paciente_Compra like '" + nAfRaiz + "%'" +
                                "AND BC.PlanMed_Cod = " + planMed + " " +
                                "AND BC.BonoConsulta_Num = " + bonoCon.Text + 
                                "AND BC.Id_Paciente_Util is null;";

                adapter = sql.execQueryGrid(query);
                adapter.Fill(table);
                bonosGrid.DataSource = table;
                labelErrorBGrid.Visible = false;
            }
            catch (SqlException)
            {
                labelErrorBGrid.Text = "Error de Consulta, intente nuevamente";
                labelErrorBGrid.Visible = true;
            }

            if (bonosGrid.Rows.Count > 0)
            {
                SelecBono.Text = "Usar bono";
                this.bonosGrid.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { this.SelecBono });
                labelErrorBGrid.Text = "Confirme el Bono Consulta";
                labelErrorBGrid.Visible = true;
            }
            else
            {
                labelErrorBCon.Text = "El bono ingresado no es correcto";
                labelErrorBCon.Visible = true;
                buttonBuscarBCon.Enabled = true;
            }

        }

        private void buttonBuscarBCon_Click(object sender, EventArgs e)
        {
            if (bonosGrid.Columns.Count == 4)
            {
                bonosGrid.Columns.RemoveAt(3);
            }
            buttonIngBono.Enabled = false;
            bonoCon.Enabled = false;
            SqlDataAdapter adapter;
            DataTable table = new DataTable();

            try
            {
                labelErrorBCon.Visible = false;
                string qSel, qFrom, qWhere, query;

                qSel = "SELECT DISTINCT BC.BonoConsulta_Num as 'Numero de Bono', BC.PlanMed_Cod as 'Plan Medico', BC.Id_Paciente_Compra as 'Afiliado que compró' ";
                qFrom = "FROM b_sharp.BonoConsulta as BC " +
                        "JOIN b_sharp.Pacientes as Pa on (Pa.Id_Paciente/100) = (BC.Id_Paciente_Compra/100) ";
                qWhere = "WHERE BC.PlanMed_Cod = " + planMed + "AND BC.Id_Paciente_Compra like '" + nAfRaiz + "%' AND BC.Id_Paciente_Util is null;";

                query = qSel + qFrom + qWhere;
                adapter = sql.execQueryGrid(query);
                adapter.Fill(table);
                bonosGrid.DataSource = table;
            }
            catch (SqlException)
            {
                labelErrorBCon.Text = "Error de consulta. Intente nuevamente";
                labelErrorBCon.Visible = true;
            }

            if (bonosGrid.Rows.Count == 0)
            {
                labelErrorBGrid.Text = "No hay bonos que pueda usar";
            }
            else
            {
                SelecBono.Text = "Usar bono";
                this.bonosGrid.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { this.SelecBono });
                labelErrorBGrid.Visible = false;
                labelErrorBCon.Visible = false;
            }

        }


        void bonosGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 3)
            {
                Decision confirmar = new Decision();
                confirmar.Text = "¿Usar bono?";
                confirmar.label1.Text = "¿Desea usar el bono?";
                confirmar.ShowDialog();
                if (confirmar.confirmar)
                {
                    try
                    {
                        updateBono(bonosGrid[0, e.RowIndex].Value);
                        crearConsulta(bonosGrid[0, e.RowIndex].Value);
                        this.Close();
                    }
                    catch
                    {
                    }
                }
            }
        }

        void updateBono(object numBono)
        {
            bool update = true;
            int nuevoNumConsulta = 0;
            try
            {
                string maxNumConsulta = "SELECT isNull(MAX(Num_Consulta),0) " +
                "FROM b_sharp.BonoConsulta " +
                "WHERE Id_Paciente_Util = @IdPac;";
                SqlCommand maxNum = new SqlCommand(maxNumConsulta, sql.openCon());
                maxNum.Parameters.AddWithValue("@IdPac", idAf.Text);
                nuevoNumConsulta = Convert.ToInt32(maxNum.ExecuteScalar()) + 1;
                labelErrorBGrid.Visible = false;
            }
            catch (SqlException)
            {
                update = false;
                labelErrorBGrid.Text = "Error en la actualización del bono";
                labelErrorBGrid.Visible = true;
            }
            if (update)
            {
                try
                {
                    string strUpdateBono =
                        "UPDATE b_sharp.BonoConsulta " +
                        "SET Id_Paciente_Util = @idPac, Num_Consulta = @nuNumCon " +
                        "WHERE BonoConsulta_Num = @numBon;";
                    SqlCommand updateBono = new SqlCommand(strUpdateBono, sql.openCon());
                    updateBono.Parameters.AddWithValue("@idPac", idAf.Text);
                    updateBono.Parameters.AddWithValue("@nuNumCon", nuevoNumConsulta);
                    updateBono.Parameters.AddWithValue("@numBon", numBono);
                    updateBono.ExecuteNonQuery();

                    MessageBox.Show("Bono actualizado correctamente");

                    labelErrorBGrid.Visible = true;
                }
                catch (SqlException)
                {
                    labelErrorBGrid.Text = "Error en la act. del bono";
                    labelErrorBGrid.Visible = true;
                }
            }

        }

        void crearConsulta(object bonoCon)
        {
            try
            {
                string stInCon = "INSERT INTO b_sharp.Consultas " +
                                 "(Id_Paciente, Id_Medico, Turno_Num, Consulta_FechaLlegada, BonoConsulta_Numero) " +
                                 "VALUES (@IdPa, @IdMed, @TurNum, @Fec, @BCNum);";
                SqlCommand insCons = new SqlCommand(stInCon, sql.openCon());
                insCons.Parameters.AddWithValue("@IdPa", idAf.Text);
                insCons.Parameters.AddWithValue("@IdMed", idMed);
                insCons.Parameters.AddWithValue("@TurNum", turnoNum);
                insCons.Parameters.AddWithValue("@Fec", fechaActual);
                insCons.Parameters.AddWithValue("@BCNum", bonoCon);
                insCons.ExecuteNonQuery();
                labelErrorBGrid.Visible = false;
                MessageBox.Show("La consulta fue creada exitosamente");
            }
            catch (SqlException)
            {
                labelErrorBGrid.Text = "Error en la creación de consulta";
                labelErrorBGrid.Visible = true;
            }
        }

        private void buttonLimpiar_Click(object sender, EventArgs e)
        {
            while (bonosGrid.Rows.Count > 0)
                bonosGrid.Rows.RemoveAt(0);
            while (turnosGrid.Rows.Count > 0)
                turnosGrid.Rows.RemoveAt(0);
            if (bonosGrid.Columns.Count == 4)
            {
                bonosGrid.Columns.RemoveAt(3);
            }
            if (turnosGrid.Columns.Count == 7)
            {
                turnosGrid.Columns.RemoveAt(6);
            }

            foreach (Control control in this.Controls)
            {
                if (control.GetType().ToString() == "System.Windows.Forms.TextBox")
                {
                    control.ResetText();
                }

                if (control.GetType().ToString() == "System.Windows.Forms.GroupBox")
                    foreach (Control ctrl in ((System.Windows.Forms.GroupBox)control).Controls)
                    {
                        if (ctrl.GetType().ToString() == "System.Windows.Forms.TextBox")
                        {
                            ctrl.ResetText();
                        }
                    }
            }
        }

        private void buttonBuscarMed_Click(object sender, EventArgs e)
        {
            loadBuscarProf();
        }

    }
}
